﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

/// <summary>
/// Summary description for DataAccessExternal
/// </summary>
public class DataAccessExternal
{
    SqlConnection SQLConn = new SqlConnection();
    System.Data.DataTable dtUsercodes = new System.Data.DataTable("Usercodes");
    System.Data.DataTable dtCallData = new System.Data.DataTable("CallData");
    System.Data.DataTable dtDNIS = new System.Data.DataTable("DNIS");
    System.Data.DataTable dtProducts = new System.Data.DataTable("Products");
    System.Data.DataTable dtProductsWhere = new System.Data.DataTable("ProductsWhere");
    System.Data.DataTable dtOrder = new System.Data.DataTable("Order");
    string _DNIS;
    string _ContactID;
    string _CampaignName;
    string _Server = "65.100.157.132";
    string _Project = "SharedPool";
    
    public DataAccessExternal()
	{
        _DNIS = HttpContext.Current.Session["dnis"].ToString();
        _ContactID = HttpContext.Current.Session["listid"].ToString();
        _CampaignName = HttpContext.Current.Session["campaignname"].ToString();
        ConnectInbound(_CampaignName, _DNIS, _ContactID);
	}

    //Actual constructor for inbound projects.  
    public bool ConnectInbound(string CampaignName, string DNIS, string ContactID)
    {
        _DNIS = DNIS;
        _ContactID = ContactID;
        _CampaignName = CampaignName;

        SQLConn.ConnectionString = "Data Source=65.100.157.132; Initial Catalog=SharedPool; User ID = webuser; Password = W3bscript$";
        SQLConn.Open();
        if (SQLConn.State == System.Data.ConnectionState.Open)
        {
            //Fill dtUsercodes Table
            string SQLUserCodes = "EXEC [f_GetUserCodesForAgent] '" + CampaignName + "' ";
            SqlCommand SqlCmdUsercodes = new SqlCommand(SQLUserCodes, SQLConn);
            SqlCmdUsercodes.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterUsercodes = new SqlDataAdapter(SqlCmdUsercodes);
            dataAdapterUsercodes.Fill(dtUsercodes);

            //Fill dtCallData Table
            string SQLCallData = "Select * from " + CampaignName + "_CallData (nolock) where enterpriselistid = " + ContactID + " ";
            SqlCommand SqlCmdCallData = new SqlCommand(SQLCallData, SQLConn);
            SqlCmdCallData.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterCallData = new SqlDataAdapter(SqlCmdCallData);
            dataAdapterCallData.Fill(dtCallData);


            //Fill dtDNIS Table
            string SQLDNIS = "EXEC [f_GetDNISInfo] '" + DNIS + "' ";
            SqlCommand SqlCmdDNIS = new SqlCommand(SQLDNIS, SQLConn);
            SqlCmdDNIS.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterDNIS = new SqlDataAdapter(SqlCmdDNIS);
            dataAdapterDNIS.Fill(dtDNIS);

            //Fill dtProducts Table
            string SQLProducts = "EXEC [f_GetProducts] '" + _CampaignName + "' ";
            SqlCommand SqlCmdProducts = new SqlCommand(SQLProducts, SQLConn);
            SqlCmdProducts.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterProducts = new SqlDataAdapter(SqlCmdProducts);
            dataAdapterProducts.Fill(dtProducts);

            //Fill dtProductsWhere
            string SQLProductsWhere = "Select * from VoiceNet_Focus..WebProducts with (nolock) where CampaignName = '" + _CampaignName + "' ";
            SqlCommand SqlCmdProductsWhere = new SqlCommand(SQLProductsWhere, SQLConn);
            SqlCmdProductsWhere.CommandType = System.Data.CommandType.Text;
            SqlDataAdapter dataAdapterProductsWhere = new SqlDataAdapter(SqlCmdProductsWhere);
            dataAdapterProductsWhere.Fill(dtProductsWhere);


            //Create Order table
            dtOrder.Columns.Add("OrderID");
            dtOrder.Columns.Add("enterpriselistid");
            dtOrder.Columns.Add("CampaignID");
            dtOrder.Columns.Add("ProductID");
            dtOrder.Columns.Add("ProductTypeID");
            dtOrder.Columns.Add("ProductDesc");
            dtOrder.Columns.Add("BaseRevenue");
            dtOrder.Columns.Add("SecondaryRevenue");
            dtOrder.Columns.Add("Quantity");




            if (dtUsercodes.Rows.Count > 0 && dtCallData.Rows.Count > 0 && dtDNIS.Rows.Count > 0 )
            {
                SQLConn.Close();
                return true;
            }
            else
            {
                SQLConn.Close();
                return false;
            }

        }
        else
            return false;
    }

    public string EnryptData(string str)
    {
        com.focusservices.fast.EncryptData FAST = new com.focusservices.fast.EncryptData();
        return FAST.CallEncryptData(str);
    }


    public string DecryptData(string str)
    {
        com.focusservices.fast.EncryptData FAST = new com.focusservices.fast.EncryptData();
        return FAST.DecryptData(str, "AF7CD-AAXZY-32acTQZ-ZZIV4R9QZ");
    }


    //get information from the in-memory tables
    public string GetDNIS(string ColumnName)
    {
        return Convert.ToString(dtDNIS.Rows[0][ColumnName]);
    }

    public string GetCallData(string ColumnName)
    {
        return Convert.ToString(dtCallData.Rows[0][ColumnName]);
    }

    public System.Data.DataTable GetUsercodes()
    {
        return dtUsercodes;
    }

    public System.Data.DataTable GetProducts()
    {
        return dtProducts;
    }


    public System.Data.DataTable GetProducts(string Where)  //This GetProducts Overload Actually accesses the Database and should be used sparingly
    {
        //Fill dtProductsWhere Table
        dtProductsWhere.Rows.Clear();
        string SQLProductsWhere = "Select * from VoiceNet_Focus..WebProducts with (nolock) where CampaignName = '" + _CampaignName + "' AND " + Where + " ";
        SqlCommand SqlCmdProductsWhere = new SqlCommand(SQLProductsWhere, SQLConn);
        SqlCmdProductsWhere.CommandType = System.Data.CommandType.Text;
        SqlDataAdapter dataAdapterProductsWhere = new SqlDataAdapter(SqlCmdProductsWhere);
        dataAdapterProductsWhere.Fill(dtProductsWhere);
        return dtProductsWhere;
    }

    public System.Data.DataTable GetProducts(string ColumnName, string EqualsValue)
    {
        dtProductsWhere.Rows.Clear();
        int NumberOfColumns = dtProducts.Columns.Count;
        for (int i = 0; i < dtProducts.Rows.Count; i++)
        {
            if (dtProducts.Rows[i][ColumnName] == EqualsValue)
            {
                System.Data.DataRow workRow = dtProductsWhere.NewRow();
                for (int index = 1; index < NumberOfColumns; index++)
                {
                    workRow[ColumnName] = dtProducts.Rows[i][ColumnName];
                }
                dtProductsWhere.Rows.Add(workRow);
            }
        }
        return dtProductsWhere;
    }

    public void PutOrderItem(int ProductID, int Quantity)
    {
        if (ProductID != -1)
        {
            System.Data.DataRow workRow = dtOrder.NewRow();
            for (int i = 0; i < dtProducts.Rows.Count; i++)
            {
                if (dtProducts.Rows[i]["ProductID"].ToString() == ProductID.ToString())
                {
                    workRow["CampaignID"] = dtProducts.Rows[i]["FastCampaignID"];
                    workRow["ProductTypeID"] = dtProducts.Rows[i]["ProductTypeID"];
                    workRow["ProductDesc"] = dtProducts.Rows[i]["ProductDesc"];
                    workRow["BaseRevenue"] = dtProducts.Rows[i]["BaseRevenue"];
                    workRow["SecondaryRevenue"] = dtProducts.Rows[i]["OtherRevenue"];
                    workRow["ProductID"] = dtProducts.Rows[i]["ProductID"];
                }
            }


            workRow["enterpriselistid"] = _ContactID.ToString();
            workRow["Quantity"] = Quantity;
            dtOrder.Rows.Add(workRow);
        }

    }

    public void RemoveOrderItem(int ProductID, int Quantity)
    {
        bool exit = false;
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            if (exit == false)
            {
                if (System.Convert.ToInt32(dtOrder.Rows[i]["ProductID"]) == ProductID && System.Convert.ToInt32(dtOrder.Rows[i]["Quantity"]) == Quantity)
                {
                    dtOrder.Rows.RemoveAt(i);
                    exit = true;
                }
            }
        }
    }

    public System.Data.DataTable GetOrder()
    {
        return dtOrder;
    }

    public double GetOrderTotal()
    {
        double OrderTotal = 0.00;
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            OrderTotal = OrderTotal + Convert.ToDouble(Convert.ToDouble(dtOrder.Rows[i]["BaseRevenue"]) * Convert.ToDouble(dtOrder.Rows[i]["Quanity"]));
        }
        return OrderTotal;
    }

    public void CommitOrder()
    {
        SQLConn.Open();
        for (int i = 0; i < dtOrder.Rows.Count; i++)
        {
            int NumberOfColumns = dtOrder.Columns.Count;
            string column = "";
            string SetStatement = "";
            string value = "";
            for (int index = 1; index < NumberOfColumns; index++)
            {

                column = dtOrder.Columns[index].ColumnName.ToString();
                value = dtOrder.Rows[i][column].ToString();
                SetStatement = SetStatement + " '" + value + "',";

            }
            SetStatement = SetStatement.TrimEnd(',');
            string SQLOrderInsert = "INSERT INTO Orders (enterpriselistid, CampaignID, ProductID, ProductTypeID, ProductDesc, BaseRevenue, SecondaryRevenue, Quantity) VALUES (" + SetStatement + ") ";

            SqlCommand SqlCmdOrderUpdate = new SqlCommand(SQLOrderInsert, SQLConn);
            SqlCmdOrderUpdate.CommandType = System.Data.CommandType.Text;
            SqlCmdOrderUpdate.ExecuteNonQuery();
        }
        SQLConn.Close();
    }

    //Overload of PutCallData to include just ColumnName and Data, but NOT datatype
    public void PutCallData(string ColumnName, string Data)
    {
        dtCallData.Rows[0][ColumnName] = Data;
    }

    public void CommitCallData()
    {
        int NumberOfColumns = dtCallData.Columns.Count;
        string column;
        string SetStatement = "";
        string value;
        for (int index = 0; index < NumberOfColumns; index++)
        {

            column = dtCallData.Columns[index].ColumnName.ToString();
            value = dtCallData.Rows[0][column].ToString();
            SetStatement = SetStatement + " " + column + " = '" + value + "',";

        }
        SetStatement = SetStatement.TrimEnd(',');
        string SQLCallDataUpdate = "UPDATE " + _CampaignName + "_CallData SET " + SetStatement + " WHERE enterpriselistid = '" + _ContactID + "' ";
        SQLConn.Open();
        SqlCommand SqlCmdCallDataUpdate = new SqlCommand(SQLCallDataUpdate, SQLConn);
        SqlCmdCallDataUpdate.CommandType = System.Data.CommandType.Text;
        SqlCmdCallDataUpdate.ExecuteNonQuery();
        SQLConn.Close();

    }

    public bool IsValidEmail(string Email)
    {
        // Return true if strIn is in valid e-mail format.
        return Regex.IsMatch(Email,
               @"^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))" +
               @"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$");
    }

    //Enumerator for Card Types
    public enum CardType
    { MasterCard, BankCard, Visa, AmericanExpress, Discover, DinersClub, JCB };

    //Validate a Credit Card Number
    public bool IsValidCC(CardType cardType, string cardNumber)
    {
        byte[] number = new byte[16]; // number to validate

        // Remove non-digits
        int len = 0;
        for (int i = 0; i < cardNumber.Length; i++)
        {
            if (char.IsDigit(cardNumber, i))
            {
                if (len == 16) return false; // number has too many digits
                number[len++] = byte.Parse(cardNumber[i].ToString()); //number[len++] = byte.Parse(cardNumber[i]);
            }
        }

        // Validate based on card type, first if tests length, second tests prefix
        switch (cardType)
        {
            case CardType.MasterCard:
                if (len != 16)
                    return false;
                if (number[0] != 5 || number[1] == 0 || number[1] > 5)
                    return false;
                break;

            case CardType.BankCard:
                if (len != 16)
                    return false;
                if (number[0] != 5 || number[1] != 6 || number[2] > 1)
                    return false;
                break;

            case CardType.Visa:
                if (len != 16 && len != 13)
                    return false;
                if (number[0] != 4)
                    return false;
                break;

            case CardType.AmericanExpress:
                if (len != 15)
                    return false;
                if (number[0] != 3 || (number[1] != 4 && number[1] != 7))
                    return false;
                break;

            case CardType.Discover:
                if (len != 16)
                    return false;
                if (number[0] != 6 || number[1] != 0 || number[2] != 1 || number[3] != 1)
                    return false;
                break;

            case CardType.DinersClub:
                if (len != 14)
                    return false;
                if (number[0] != 3 || (number[1] != 0 && number[1] != 6 && number[1] != 8) || number[1] == 0 && number[2] > 5)
                    return false;
                break;

            case CardType.JCB:
                if (len != 16 && len != 15)
                    return false;
                if (number[0] != 3 || number[1] != 5)
                    return false;
                break;
        }

        // Use Luhn Algorithm to validate
        int sum = 0;
        for (int i = len - 1; i >= 0; i--)
        {
            if (i % 2 == len % 2)
            {
                int n = number[i] * 2;
                sum += (n / 10) + (n % 10);
            }
            else
                sum += number[i];
        }
        return (sum % 10 == 0);
    }



    /// Validates the routing number.
    ///
    public bool ValidateRoutingNumber(int routingNumber)
    {
        //The check equation for a number a1a2a3a4a5a6a7a8a9 is 3a1 + 7a2 + a3 + 3a4 + 7a5 + a6 + 3a7 + 7 a8 + a9 mod 10 = 0

        //rewrote for higher efficiency

        //must be 9 digits
        if (routingNumber < 100000000)
            return false;
        if (routingNumber > 999999999)
            return false;
        //build array for quick comparison
        //left to right using fun with math.

        int[] routingNumberAsArray =
        {
            routingNumber / 100000000,
            (routingNumber % 100000000) / 10000000,
            (routingNumber % 10000000) / 1000000,
            (routingNumber % 1000000) / 100000,
            (routingNumber % 100000) / 10000,
            (routingNumber % 10000) / 1000,
            (routingNumber % 1000) / 100,
            (routingNumber % 100) / 10,
            (routingNumber % 10) / 1
        };

        //calculate the route sum using the formula above. 3 7 1.
        int routeSum =
            3 * routingNumberAsArray[0] +
            7 * routingNumberAsArray[1] +
            routingNumberAsArray[2] +
            3 * routingNumberAsArray[3] +
            7 * +routingNumberAsArray[4] +
            routingNumberAsArray[5] +
            3 * routingNumberAsArray[6] +
            7 * routingNumberAsArray[7] +
            routingNumberAsArray[8];
        return routeSum % 10 == 0;
    }
}